﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.IO;
using NPOI.HSSF.UserModel;
using IHISCETeim.BLL;
using IHISCETeim.Library;
using IHISCETeim.Model;
using IHISCETeim.DAL;

namespace IHISCETeim.FXTool
{
    public partial class WeiShangBaoImportFrm : Form
    {
        public WeiShangBaoImportFrm()
        {
            InitializeComponent();
        }
        OUserInfo objNews = null;
        private LoginState IsAuthenticated(string userName, string pwd)
        {
            try
            {
                objNews = CBO.FillObject<OUserInfo>(OUserDAL.UserLogin(userName, StringHelper.GetPwdEncrypt(pwd), ""));
                // UserId = objNews.UserId;
                if (objNews == null)
                {
                    return LoginState.inputerror;
                }
                else if (objNews.State == 0)
                {
                    return LoginState.other;
                }
                else
                {
                    return LoginState.success;
                }
            }
            catch(Exception exc)
            {
                MessageBox.Show(exc.Message);
                // UserId = 0;
                return LoginState.other;
            }
            finally
            {

            }
        }
        private void btnImport_Click(object sender, EventArgs e)
        {
            try
            {
                //InitializeWorkbook(@"xls\Book1.xls");
                //ConvertToDataTable();

                //gvData.DataSource = dataSet1.Tables[0];
                string username = this.txtUserName.Text;
                string pwd = this.txtPwd.Text;

                LoginState ls = IsAuthenticated(username, pwd);
                if (ls != LoginState.success)
                {
                    MessageBox.Show("登陆失败，请确认你的用户密码");
                    return;
                }

                this.txtImportResult.Text = "";

                FXFinanceLeaseController.DeleteWeiShangBao();
                this.txtImportResult.Text += "已清楚之前未上报记录" + "\r\n"; ;

                DataTable importdt = this.dataSet1.Tables[0];
                string AliasName = "";
                decimal LContractAmount = 0m;
                string CustomerName = "";
                string SerialNumber = "";
                string SaleDate = "";

                foreach (DataRow dr in importdt.Rows)
                {
                    AliasName = dr["经销商"].ToString();
                    LContractAmount = Convert.ToDecimal(dr["合同价格"]);
                    CustomerName = dr["用户"].ToString();
                    SerialNumber = dr["机号"].ToString();
                    SaleDate = Convert.ToDateTime(dr["销售日期"]).ToString("yyyy-MM-dd");// 销售日期 模板的单元格格式一定要设置成 日期 2001/3/14 这格式

                    string x = FXFinanceLeaseController.ImportFromExcelWeiShangBao(AliasName, CustomerName, SerialNumber, SaleDate, LContractAmount);
                    if (!string.IsNullOrEmpty(x))
                        this.txtImportResult.Text += x + "\r\n";
                }
                MessageBox.Show("已完成导入");
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message, "出错提示");
            }

        }

        #region 打开Execl
        HSSFWorkbook hssfworkbook;

        void InitializeWorkbook(string path)
        {
            //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
            //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added. 
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);


            }
        }

        void ConvertToDataTable()
        {
            HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();
            //for (int j = 0; j < 5; j++)
            //{
            //    dt.Columns.Add(Convert.ToChar(((int)'A')+j).ToString());
            //}
            //rows.MoveNext();
            int titleRowNum = sheet.FirstRowNum;
            HSSFRow titlerow = (HSSFRow)sheet.GetRow(titleRowNum);
            for (int j = 0; j < titlerow.LastCellNum; j++)
            {
                if (titlerow.Cells[j].ToString() == "销售日期")
                {
                    dt.Columns.Add(titlerow.Cells[j].ToString(),typeof(DateTime));
                }
                else
                {
                    dt.Columns.Add(titlerow.Cells[j].ToString());
                }
            }
            rows.MoveNext();
            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    HSSFCell cell = row.GetCell(i) as HSSFCell;


                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            dataSet1.Tables.Add(dt);
        }
        #endregion

        private void btnSelectFile_Click(object sender, EventArgs e)
        {
            string path;

            //OpenFileDialog of = new OpenFileDialog();

            // of.Filter = "Excel文件(*.xls)|*.xls";
            if (this.openFileDialog1.ShowDialog() != System.Windows.Forms.DialogResult.OK)
            {
                return;
            }

            path = openFileDialog1.FileName;

            this.txtFilePath.Text = path;
            ////

            InitializeWorkbook(path);
            ConvertToDataTable();

            gvData.DataSource = dataSet1.Tables[0];
            // this.btnBegin.IsEnabled = true;

            this.txtMsg.Text = string.Format("本次需要导入[{0}]条,请核对需导入的数据与Excel是否一致.", dataSet1.Tables[0].Rows.Count);

        }
    }
}
